1 Imports System.Data.SqlClient
2 Imports System.Data
3 Imports Excel = Microsoft.Office.Interop.Excel
4 Public Class frmFullDueList
5 Sub fillSession()
6 Try
7 con = New SqlConnection(cs)
8 con.Open()
9 adp = New SqlDataAdapter()
10 adp.SelectCommand = New SqlCommand("SELECT distinct (Session) FROM Session_Master", con)
11 ds = New DataSet("ds")
12 adp.Fill(ds)
13 dtable = ds.Tables(0)
14 cmbSession.Items.Clear()
15 cmbSession1.Items.Clear()
16 cmbSession2.Items.Clear()
17 For Each drow As DataRow In dtable.Rows
18 cmbSession.Items.Add(drow(0).ToString())
19 cmbSession1.Items.Add(drow(0).ToString())
20 cmbSession2.Items.Add(drow(0).ToString())
21 Next
22 Catch ex As Exception
23 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
24 End Try
25 End Sub
26 Sub fillClass()
27 Try
28 con = New SqlConnection(cs)
29 con.Open()
30 adp = New SqlDataAdapter()
31 adp.SelectCommand = New SqlCommand("SELECT distinct (ClassName) FROM Class", con)
32 ds = New DataSet("ds")
33 adp.Fill(ds)
34 dtable = ds.Tables(0)
35 cmbClass.Items.Clear()
36 cmbClass1.Items.Clear()
37 cmbClass2.Items.Clear()
38 For Each drow As DataRow In dtable.Rows
39 cmbClass.Items.Add(drow(0).ToString())
40 cmbClass1.Items.Add(drow(0).ToString())
41 cmbClass2.Items.Add(drow(0).ToString())
42 Next
43 Catch ex As Exception
44 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
45 End Try
46 End Sub
47
48 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
49 Try
50 If Len(Trim(cmbSession.Text)) = 0 Then
51 MessageBox.Show("Please select session", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
52 cmbSession.Focus()
53 Exit Sub
54 End If
55 If Len(Trim(cmbClass.Text)) = 0 Then
56 MessageBox.Show("Please select class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
57 cmbClass.Focus()
58 Exit Sub
59 End If
60 If Len(Trim(cmbSemester.Text)) = 0 Then
61 MessageBox.Show("Please select semester", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
62 cmbSemester.Focus()
63 Exit Sub
64 End If
65 con = New SqlConnection(cs)
66 con.Open()
67 cmd = New SqlCommand("SELECT RTRIM(Student.AdmissionNo),RTRIM(GRNo),RTRIM(StudentName),RTRIM(SchoolName),Sum(Fee) from Student,SchoolInfo,CourseFee,Class where Student.SchoolID=SchoolInfo.S_ID and Class.Classname=CourseFee.Class and CourseFee.SchoolID=SchoolInfo.S_ID and Student.Session=@d1 and CourseFee.Class=@d2 and CourseFee.Semester=@d3 group by Student.AdmissionNo,GRNo,StudentName,SchoolName Except SELECT RTRIM(Student.AdmissionNo),RTRIM(GRNo),RTRIM(StudentName),RTRIM(SchoolName),Sum(Fee) from Student,SchoolInfo,CourseFee,CourseFeePayment,Class where CourseFee.SchoolID=SchoolInfo.S_ID and Class.Classname=CourseFee.Class and Student.SchoolID=SchoolInfo.S_ID and CourseFeePayment.Class=CourseFee.Class and Student.AdmissionNo=CourseFeePayment.AdmissionNo and CourseFee.Semester=CourseFeePayment.Semester and CourseFeePayment.Session=@d1 and CourseFee.Class=@d2 and CourseFee.Semester=@d3 group by Student.AdmissionNo,GRNo,StudentName,SchoolName order by 3", con)
68 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
69 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
70 cmd.Parameters.AddWithValue("@d3", cmbSemester.Text)
71 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
72 dgw.Rows.Clear()
73 While (rdr.Read() = True)
74 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4))
75 End While
76 con.Close()
77 Catch ex As Exception
78 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
79 End Try
80 End Sub
81
82 Private Sub cmbClass_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbClass.SelectedIndexChanged
83 cmbSemester.Enabled = True
84 End Sub
85
86 Private Sub btnReset_Click(sender As System.Object, e As System.EventArgs) Handles btnReset.Click
87 cmbSession.SelectedIndex = -1
88 cmbClass.SelectedIndex = -1
89 cmbSemester.SelectedIndex = -1
90 cmbSemester.Enabled = False
91 cmbClass.Enabled = False
92 dgw.Rows.Clear()
93 End Sub
94
95 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
96 Dim rowsTotal, colsTotal As Short
97 Dim I, j, iC As Short
98 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
99 Dim xlApp As New Excel.Application
100 Try
101 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
102 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
103 xlApp.Visible = True
104
105 rowsTotal = dgw.RowCount
106 colsTotal = dgw.Columns.Count - 1
107 With excelWorksheet
108 .Cells.Select()
109 .Cells.Delete()
110 For iC = 0 To colsTotal
111 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
112 Next
113 For I = 0 To rowsTotal - 1
114 For j = 0 To colsTotal
115 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
116 Next j
117 Next I
118 .Rows("1:1").Font.FontStyle = "Bold"
119 .Rows("1:1").Font.Size = 12
120
121 .Cells.Columns.AutoFit()
122 .Cells.Select()
123 .Cells.EntireColumn.AutoFit()
124 .Cells(1, 1).Select()
125 End With
126 Catch ex As Exception
127 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
128 Finally
129 'RELEASE ALLOACTED RESOURCES
130 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
131 xlApp = Nothing
132 End Try
133 End Sub
134 Sub Reset()
135 cmbSession.SelectedIndex = -1
136 cmbClass.SelectedIndex = -1
137 cmbSemester.SelectedIndex = -1
138 cmbSemester.Enabled = False
139 cmbClass.Enabled = False
140 dgw.Rows.Clear()
141 cmbSession1.SelectedIndex = -1
142 cmbClass1.SelectedIndex = -1
143 cmbInstallment.SelectedIndex = -1
144 cmbClass1.Enabled = False
145 cmbInstallment.Enabled = False
146 dgw1.Rows.Clear()
147 cmbSession2.SelectedIndex = -1
148 cmbClass2.SelectedIndex = -1
149 cmbInstallment1.SelectedIndex = -1
150 cmbInstallment1.Enabled = False
151 cmbClass2.Enabled = False
152 dgw2.Rows.Clear()
153 fillSession()
154 fillClass()
155 End Sub
156
157 Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
158 Me.Close()
159 End Sub
160
161 Private Sub frmPartialDueList_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
162 fillSession()
163 fillClass()
164 End Sub
165
166 Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click
167 cmbSession1.SelectedIndex = -1
168 cmbClass1.SelectedIndex = -1
169 cmbInstallment.SelectedIndex = -1
170 cmbClass1.Enabled = False
171 cmbInstallment.Enabled = False
172 dgw1.Rows.Clear()
173 End Sub
174
175 Private Sub Button10_Click(sender As System.Object, e As System.EventArgs) Handles Button10.Click
176 cmbSession2.SelectedIndex = -1
177 cmbClass2.SelectedIndex = -1
178 cmbInstallment1.SelectedIndex = -1
179 cmbInstallment1.Enabled = False
180 cmbClass2.Enabled = False
181 dgw2.Rows.Clear()
182 End Sub
183
184 Private Sub Button9_Click(sender As System.Object, e As System.EventArgs) Handles Button9.Click
185 Dim rowsTotal, colsTotal As Short
186 Dim I, j, iC As Short
187 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
188 Dim xlApp As New Excel.Application
189 Try
190 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
191 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
192 xlApp.Visible = True
193
194 rowsTotal = dgw2.RowCount
195 colsTotal = dgw2.Columns.Count - 1
196 With excelWorksheet
197 .Cells.Select()
198 .Cells.Delete()
199 For iC = 0 To colsTotal
200 .Cells(1, iC + 1).Value = dgw2.Columns(iC).HeaderText
201 Next
202 For I = 0 To rowsTotal - 1
203 For j = 0 To colsTotal
204 .Cells(I + 2, j + 1).value = dgw2.Rows(I).Cells(j).Value
205 Next j
206 Next I
207 .Rows("1:1").Font.FontStyle = "Bold"
208 .Rows("1:1").Font.Size = 12
209
210 .Cells.Columns.AutoFit()
211 .Cells.Select()
212 .Cells.EntireColumn.AutoFit()
213 .Cells(1, 1).Select()
214 End With
215 Catch ex As Exception
216 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
217 Finally
218 'RELEASE ALLOACTED RESOURCES
219 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
220 xlApp = Nothing
221 End Try
222 End Sub
223
224 Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
225 Dim rowsTotal, colsTotal As Short
226 Dim I, j, iC As Short
227 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
228 Dim xlApp As New Excel.Application
229 Try
230 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
231 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
232 xlApp.Visible = True
233
234 rowsTotal = dgw1.RowCount
235 colsTotal = dgw1.Columns.Count - 1
236 With excelWorksheet
237 .Cells.Select()
238 .Cells.Delete()
239 For iC = 0 To colsTotal
240 .Cells(1, iC + 1).Value = dgw1.Columns(iC).HeaderText
241 Next
242 For I = 0 To rowsTotal - 1
243 For j = 0 To colsTotal
244 .Cells(I + 2, j + 1).value = dgw1.Rows(I).Cells(j).Value
245 Next j
246 Next I
247 .Rows("1:1").Font.FontStyle = "Bold"
248 .Rows("1:1").Font.Size = 12
249
250 .Cells.Columns.AutoFit()
251 .Cells.Select()
252 .Cells.EntireColumn.AutoFit()
253 .Cells(1, 1).Select()
254 End With
255 Catch ex As Exception
256 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
257 Finally
258 'RELEASE ALLOACTED RESOURCES
259 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
260 xlApp = Nothing
261 End Try
262 End Sub
263
264 Private Sub cmbSession1_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSession1.SelectedIndexChanged
265 cmbClass1.Enabled = True
266 End Sub
267
268 Private Sub cmbSession2_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSession2.SelectedIndexChanged
269 Try
270 cmbClass2.Enabled = True
271 con = New SqlConnection(cs)
272 con.Open()
273 Dim ct As String = "SELECT distinct RTRIM(Class) FROM Class,Section,Student where Class.ClassName=Section.Class and Student.SectionID=Section.ID"
274 cmd = New SqlCommand(ct)
275 cmd.Connection = con
276 cmd.Parameters.AddWithValue("@d1", cmbSession2.Text)
277 rdr = cmd.ExecuteReader()
278 cmbClass2.Items.Clear()
279 While rdr.Read
280 cmbClass2.Items.Add(rdr(0))
281 End While
282 con.Close()
283 Catch ex As Exception
284 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
285 End Try
286 End Sub
287
288 Private Sub TabControl1_Click(sender As System.Object, e As System.EventArgs) Handles TabControl1.Click
289 Reset()
290 End Sub
291
292 Private Sub Button7_Click(sender As System.Object, e As System.EventArgs) Handles Button7.Click
293 Try
294 If Len(Trim(cmbSession1.Text)) = 0 Then
295 MessageBox.Show("Please select session", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
296 cmbSession1.Focus()
297 Exit Sub
298 End If
299 If Len(Trim(cmbClass1.Text)) = 0 Then
300 MessageBox.Show("Please select class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
301 cmbClass1.Focus()
302 Exit Sub
303 End If
304 If Len(Trim(cmbInstallment.Text)) = 0 Then
305 MessageBox.Show("Please select installment", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
306 cmbInstallment.Focus()
307 Exit Sub
308 End If
309 con = New SqlConnection(cs)
310 con.Open()
311 cmd = New SqlCommand("SELECT RTRIM(Student.AdmissionNo),RTRIM(GRNo),RTRIM(StudentName),RTRIM(HostelName),RTRIM(SchoolName),SUM(Charges) FROM Student,Hosteler,HostelInfo,Installment_Hostel,schoolInfo,Class where Student.AdmissionNo=Hosteler.AdmissionNo and HostelInfo.HI_ID=Hosteler.HostelID and Installment_Hostel.HostelID=HostelInfo.HI_ID and Student.SchoolID=SchoolInfo.S_ID and Installment_Hostel.SchoolID=SchoolInfo.S_ID and Installment_Hostel.Class=Class.Classname and Student.Session=@d1 and Installment_Hostel.Class=@d2 and Installment=@d3 group by student.admissionno,GRNO,StudentName,SchoolName,HostelName Except SELECT RTRIM(Student.AdmissionNo),RTRIM(GRNo),RTRIM(StudentName),RTRIM(SchoolName),RTRIM(HostelName),SUM(Charges) FROM Student,Hosteler,HostelInfo,Installment_Hostel,schoolInfo,Class,HostelFeePayment where Student.AdmissionNo=Hosteler.AdmissionNo and HostelInfo.HI_ID=Hosteler.HostelID and Installment_Hostel.HostelID=HostelInfo.HI_ID and Student.SchoolID=SchoolInfo.S_ID and Installment_Hostel.SchoolID=SchoolInfo.S_ID and Installment_Hostel.Class=Class.Classname and HostelFeePayment.HostelerID=Hosteler.H_ID and Installment_Hostel.Installment=HostelFeePayment.Installment and HostelFeePayment.Session=@d1 and Installment_Hostel.Class=@d2 and Installment_Hostel.Installment=@d3 group by student.admissionno,GRNO,StudentName,SchoolName,HostelName order by 3 ", con)
312 cmd.Parameters.AddWithValue("@d1", cmbSession1.Text)
313 cmd.Parameters.AddWithValue("@d2", cmbClass1.Text)
314 cmd.Parameters.AddWithValue("@d3", cmbInstallment.Text)
315 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
316 dgw1.Rows.Clear()
317 While (rdr.Read() = True)
318 dgw1.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5))
319 End While
320 con.Close()
321 Catch ex As Exception
322 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
323 End Try
324 End Sub
325
326 Private Sub Button11_Click(sender As System.Object, e As System.EventArgs) Handles Button11.Click
327 Try
328 If Len(Trim(cmbSession2.Text)) = 0 Then
329 MessageBox.Show("Please select session", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
330 cmbSession2.Focus()
331 Exit Sub
332 End If
333 If Len(Trim(cmbClass2.Text)) = 0 Then
334 MessageBox.Show("Please select class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
335 cmbClass2.Focus()
336 Exit Sub
337 End If
338 If Len(Trim(cmbInstallment1.Text)) = 0 Then
339 MessageBox.Show("Please select installment", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
340 cmbInstallment1.Focus()
341 Exit Sub
342 End If
343 con = New SqlConnection(cs)
344 con.Open()
345 cmd = New SqlCommand("SELECT RTRIM(Student.AdmissionNo),RTRIM(GRNo),RTRIM(StudentName),RTRIM(Location.LocationName),RTRIM(SchoolName),SUM(Charges) FROM Student,BusCardHolder_Student,Installment_Bus,schoolInfo,Location,Class,Section where BusCardHolder_Student.Location=Location.LocationName and Student.AdmissionNo=BusCardHolder_Student.AdmissionNo and Installment_Bus.Location=Location.LocationName and Student.SchoolID=SchoolInfo.S_ID and Class.Classname=Section.Class and Section.ID=Student.SectionID and Student.Session=@d1 and Class.ClassName=@d2 and Installment_Bus.Installment=@d3 group by student.admissionno,GRNO,StudentName,SchoolName,LocationName Except SELECT RTRIM(Student.AdmissionNo),RTRIM(GRNo),RTRIM(StudentName),RTRIM(LocationName),RTRIM(SchoolName),SUM(Charges) FROM Student,BusCardHolder_Student,Installment_Bus,schoolInfo,Location,Class,Section,BusFeePayment_Student where BusCardHolder_Student.Location=Location.LocationName and Student.AdmissionNo=BusCardHolder_Student.AdmissionNo and Installment_Bus.Location=Location.LocationName and Student.SchoolID=SchoolInfo.S_ID and Class.Classname=Section.Class and Section.ID=Student.SectionID and BusFeePayment_Student.BusHolderID=BusCardHolder_Student.BCH_ID and BusFeePayment_Student.Installment=Installment_Bus.Installment and BusFeePayment_Student.Session=@d1 and BusFeePayment_Student.Class=@d2 and Installment_Bus.Installment=@d3 group by student.admissionno,GRNO,StudentName,SchoolName,LocationName order by 3", con)
346 cmd.Parameters.AddWithValue("@d1", cmbSession2.Text)
347 cmd.Parameters.AddWithValue("@d2", cmbClass2.Text)
348 cmd.Parameters.AddWithValue("@d3", cmbInstallment1.Text)
349 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
350 dgw2.Rows.Clear()
351 While (rdr.Read() = True)
352 dgw2.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5))
353 End While
354 con.Close()
355 Catch ex As Exception
356 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
357 End Try
358 End Sub
359
360 Private Sub cmbClass1_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbClass1.SelectedIndexChanged
361 Try
362 cmbInstallment.Enabled = True
363 con = New SqlConnection(cs)
364 con.Open()
365 Dim ct As String = "SELECT distinct Installment from Installment_Hostel where Class=@d1"
366 cmd = New SqlCommand(ct)
367 cmd.Connection = con
368 cmd.Parameters.AddWithValue("@d1", cmbClass1.Text)
369 rdr = cmd.ExecuteReader()
370 cmbInstallment.Items.Clear()
371 While rdr.Read
372 cmbInstallment.Items.Add(rdr(0))
373 End While
374 con.Close()
375 Catch ex As Exception
376 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
377 End Try
378 End Sub
379
380 Private Sub cmbClass2_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbClass2.SelectedIndexChanged
381 Try
382 cmbInstallment1.Enabled = True
383 con = New SqlConnection(cs)
384 con.Open()
385 Dim ct As String = "SELECT distinct RTRIM(Installment) from Installment_Bus"
386 cmd = New SqlCommand(ct)
387 cmd.Connection = con
388 rdr = cmd.ExecuteReader()
389 cmbInstallment1.Items.Clear()
390 While rdr.Read
391 cmbInstallment1.Items.Add(rdr(0))
392 End While
393 con.Close()
394 Catch ex As Exception
395 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
396 End Try
397 End Sub
398
399 Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
400 Try
401 If Len(Trim(cmbSession.Text)) = 0 Then
402 MessageBox.Show("Please select session", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
403 cmbSession.Focus()
404 Exit Sub
405 End If
406 If Len(Trim(cmbClass.Text)) = 0 Then
407 MessageBox.Show("Please select class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
408 cmbClass.Focus()
409 Exit Sub
410 End If
411 If Len(Trim(cmbSemester.Text)) = 0 Then
412 MessageBox.Show("Please select semester", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
413 cmbSemester.Focus()
414 Exit Sub
415 End If
416 Cursor = Cursors.WaitCursor
417 Timer1.Enabled = True
418 con = New SqlConnection(cs)
419 con.Open()
420 cmd = New SqlCommand("SELECT RTRIM(Student.AdmissionNo),RTRIM(GRNo),RTRIM(StudentName),RTRIM(SchoolName),Sum(Fee) from Student,SchoolInfo,CourseFee,Class where Student.SchoolID=SchoolInfo.S_ID and Class.Classname=CourseFee.Class and CourseFee.SchoolID=SchoolInfo.S_ID and Student.Session=@d1 and CourseFee.Class=@d2 and CourseFee.Semester=@d3 group by Student.AdmissionNo,GRNo,StudentName,SchoolName Except SELECT RTRIM(Student.AdmissionNo),RTRIM(GRNo),RTRIM(StudentName),RTRIM(SchoolName),Sum(Fee) from Student,SchoolInfo,CourseFee,CourseFeePayment,Class where CourseFee.SchoolID=SchoolInfo.S_ID and Class.Classname=CourseFee.Class and Student.SchoolID=SchoolInfo.S_ID and CourseFeePayment.Class=CourseFee.Class and Student.AdmissionNo=CourseFeePayment.AdmissionNo and CourseFee.Semester=CourseFeePayment.Semester and CourseFeePayment.Session=@d1 and CourseFee.Class=@d2 and CourseFee.Semester=@d3 group by Student.AdmissionNo,GRNo,StudentName,SchoolName order by 3", con)
421 cmd.Parameters.AddWithValue("@d1", cmbSession.Text)
422 cmd.Parameters.AddWithValue("@d2", cmbClass.Text)
423 cmd.Parameters.AddWithValue("@d3", cmbSemester.Text)
424 adp = New SqlDataAdapter(cmd)
425 dtable = New DataTable()
426 adp.Fill(dtable)
427 con.Close()
428 DataGridView1.DataSource = dtable
429 ds = New DataSet()
430 ds.Tables.Add(dtable)
431 ds.WriteXmlSchema("ClassFeeFullDue.xml")
432 Dim rpt As New rptClassFeeFullDue
433 rpt.SetDataSource(ds)
434 rpt.SetParameterValue("p1", cmbSession.Text)
435 rpt.SetParameterValue("p2", cmbClass.Text)
436 rpt.SetParameterValue("p3", cmbSemester.Text)
437 frmReport.CrystalReportViewer1.ReportSource = rpt
438 frmReport.ShowDialog()
439 Catch ex As Exception
440 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
441 End Try
442 End Sub
443
444 Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
445 Cursor = Cursors.Default
446 Timer1.Enabled = False
447 End Sub
448
449 Private Sub cmbSession_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbSession.SelectedIndexChanged
450 cmbClass.Enabled = True
451 End Sub
452
453 Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
454 Try
455 If Len(Trim(cmbSession1.Text)) = 0 Then
456 MessageBox.Show("Please select session", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
457 cmbSession1.Focus()
458 Exit Sub
459 End If
460 If Len(Trim(cmbClass1.Text)) = 0 Then
461 MessageBox.Show("Please select class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
462 cmbClass1.Focus()
463 Exit Sub
464 End If
465 If Len(Trim(cmbInstallment.Text)) = 0 Then
466 MessageBox.Show("Please select installment", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
467 cmbInstallment.Focus()
468 Exit Sub
469 End If
470 Cursor = Cursors.WaitCursor
471 Timer1.Enabled = True
472 con = New SqlConnection(cs)
473 con.Open()
474 cmd = New SqlCommand("SELECT RTRIM(Student.AdmissionNo),RTRIM(GRNo),RTRIM(StudentName),RTRIM(HostelName),RTRIM(SchoolName),SUM(Charges) FROM Student,Hosteler,HostelInfo,Installment_Hostel,schoolInfo,Class where Student.AdmissionNo=Hosteler.AdmissionNo and HostelInfo.HI_ID=Hosteler.HostelID and Installment_Hostel.HostelID=HostelInfo.HI_ID and Student.SchoolID=SchoolInfo.S_ID and Installment_Hostel.SchoolID=SchoolInfo.S_ID and Installment_Hostel.Class=Class.Classname and Student.Session=@d1 and Installment_Hostel.Class=@d2 and Installment=@d3 group by student.admissionno,GRNO,StudentName,SchoolName,HostelName Except SELECT RTRIM(Student.AdmissionNo),RTRIM(GRNo),RTRIM(StudentName),RTRIM(SchoolName),RTRIM(HostelName),SUM(Charges) FROM Student,Hosteler,HostelInfo,Installment_Hostel,schoolInfo,Class,HostelFeePayment where Student.AdmissionNo=Hosteler.AdmissionNo and HostelInfo.HI_ID=Hosteler.HostelID and Installment_Hostel.HostelID=HostelInfo.HI_ID and Student.SchoolID=SchoolInfo.S_ID and Installment_Hostel.SchoolID=SchoolInfo.S_ID and Installment_Hostel.Class=Class.Classname and HostelFeePayment.HostelerID=Hosteler.H_ID and Installment_Hostel.Installment=HostelFeePayment.Installment and HostelFeePayment.Session=@d1 and Installment_Hostel.Class=@d2 and Installment_Hostel.Installment=@d3 group by student.admissionno,GRNO,StudentName,SchoolName,HostelName order by 3 ", con)
475 cmd.Parameters.AddWithValue("@d1", cmbSession1.Text)
476 cmd.Parameters.AddWithValue("@d2", cmbClass1.Text)
477 cmd.Parameters.AddWithValue("@d3", cmbInstallment.Text)
478 adp = New SqlDataAdapter(cmd)
479 dtable = New DataTable()
480 adp.Fill(dtable)
481 con.Close()
482 DataGridView2.DataSource = dtable
483 ds = New DataSet()
484 ds.Tables.Add(dtable)
485 ds.WriteXmlSchema("HostelFeeFullDue.xml")
486 Dim rpt As New rptHostelFeeFullDue
487 rpt.SetDataSource(ds)
488 rpt.SetParameterValue("p1", cmbSession1.Text)
489 rpt.SetParameterValue("p2", cmbClass1.Text)
490 rpt.SetParameterValue("p3", cmbInstallment.Text)
491 frmReport.CrystalReportViewer1.ReportSource = rpt
492 frmReport.ShowDialog()
493 Catch ex As Exception
494 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
495 End Try
496 End Sub
497
498 Private Sub Button8_Click(sender As System.Object, e As System.EventArgs) Handles Button8.Click
499 Try
500 If Len(Trim(cmbSession2.Text)) = 0 Then
501 MessageBox.Show("Please select session", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
502 cmbSession2.Focus()
503 Exit Sub
504 End If
505 If Len(Trim(cmbClass2.Text)) = 0 Then
506 MessageBox.Show("Please select class", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
507 cmbClass2.Focus()
508 Exit Sub
509 End If
510 If Len(Trim(cmbInstallment1.Text)) = 0 Then
511 MessageBox.Show("Please select installment", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
512 cmbInstallment1.Focus()
513 Exit Sub
514 End If
515 Cursor = Cursors.WaitCursor
516 Timer1.Enabled = True
517 con = New SqlConnection(cs)
518 con.Open()
519 cmd = New SqlCommand("SELECT RTRIM(Student.AdmissionNo),RTRIM(GRNo),RTRIM(StudentName),RTRIM(Location.LocationName),RTRIM(SchoolName),SUM(Charges) FROM Student,BusCardHolder_Student,Installment_Bus,schoolInfo,Location,Class,Section where BusCardHolder_Student.Location=Location.LocationName and Student.AdmissionNo=BusCardHolder_Student.AdmissionNo and Installment_Bus.Location=Location.LocationName and Student.SchoolID=SchoolInfo.S_ID and Class.Classname=Section.Class and Section.ID=Student.SectionID and Student.Session=@d1 and Class.ClassName=@d2 and Installment_Bus.Installment=@d3 group by student.admissionno,GRNO,StudentName,SchoolName,LocationName Except SELECT RTRIM(Student.AdmissionNo),RTRIM(GRNo),RTRIM(StudentName),RTRIM(LocationName),RTRIM(SchoolName),SUM(Charges) FROM Student,BusCardHolder_Student,Installment_Bus,schoolInfo,Location,Class,Section,BusFeePayment_Student where BusCardHolder_Student.Location=Location.LocationName and Student.AdmissionNo=BusCardHolder_Student.AdmissionNo and Installment_Bus.Location=Location.LocationName and Student.SchoolID=SchoolInfo.S_ID and Class.Classname=Section.Class and Section.ID=Student.SectionID and BusFeePayment_Student.BusHolderID=BusCardHolder_Student.BCH_ID and BusFeePayment_Student.Installment=Installment_Bus.Installment and BusFeePayment_Student.Session=@d1 and BusFeePayment_Student.Class=@d2 and Installment_Bus.Installment=@d3 group by student.admissionno,GRNO,StudentName,SchoolName,LocationName order by 3", con)
520 cmd.Parameters.AddWithValue("@d1", cmbSession2.Text)
521 cmd.Parameters.AddWithValue("@d2", cmbClass2.Text)
522 cmd.Parameters.AddWithValue("@d3", cmbInstallment1.Text)
523 adp = New SqlDataAdapter(cmd)
524 dtable = New DataTable()
525 adp.Fill(dtable)
526 con.Close()
527 DataGridView3.DataSource = dtable
528 ds = New DataSet()
529 ds.Tables.Add(dtable)
530 ds.WriteXmlSchema("BusFeeFullDue_Student.xml")
531 Dim rpt As New rptBusFeeFullDue_Student
532 rpt.SetDataSource(ds)
533 rpt.SetParameterValue("p1", cmbSession2.Text)
534 rpt.SetParameterValue("p2", cmbClass2.Text)
535 rpt.SetParameterValue("p3", cmbInstallment1.Text)
536 frmReport.CrystalReportViewer1.ReportSource = rpt
537 frmReport.ShowDialog()
538 Catch ex As Exception
539 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
540 End Try
541 End Sub
542 End Class
